Migration Files
migration_broadcaster.cmd Create a new file "migration_broadcaster.cmd" Paste the following code REM Migration mssql -> mysql script REM version 2.0.1 REM author : beyoann@flexmedia.co.th REM You must provide database name, username, password SET arg_database_name= SET arg_username= SET arg_password= REM Change those parameters if the file does not have same name or are not in same folder of this file SET arg_stored_function=stored_function.sql SET arg_preparation_file=table_schema.sql SET arg_finalizing_file=finalizing.sql SET arg_migration_script=copy_migrated_tables.cmd REM *** Optional setting REM If your MySQL Server is hosted on another machine set use_another_machine to 1 SET use_another_machine=0 REM Put the server ip where the MySQL server is located (only in use if use_another_machine is set to 1) SET server_ip= REM Let's check if all settings are good IF %arg_database_name% [] ( IF %arg_username% [] ( IF %arg_password% [] ( GOTO ErrorConfig ) ) ) IF %use_another_machine% 1 ( IF %arg_server_ip% [] ( GOTO ErrorHosting ) SET arg_hosting=-h %server_ip% ) SET arg_mysql_options=%arg_hosting% --user=%arg_username% --password=%arg_password% %arg_database_name% IF EXIST %arg_preparation_file% ( IF EXIST %arg_finalizing_file% ( IF EXIST %arg_migration_script% ( IF EXIST %arg_stored_function% ( SET STARTTIME = %TIME% ECHO Preparing Table Schema @mysql %arg_mysql_options% %arg_preparation_file% ECHO Migrating Data CALL %arg_migration_script% ECHO Finalizing @mysql %arg_mysql_options% %arg_finalizing_file% SET ENDTIME = %TIME% Goto Done ) ) ) ) GOTO ErrorFileMissing :Done FOR /F "tokens=1-4 delims=:.," %%a IN ("%STARTTIME%") DO ( SET /A "start=(((%%a*60)+1%%b %% 100)*60+1%%c %% 100)*100+1%%d %% 100" ) FOR /F "tokens=1-4 delims=:.," %%a IN ("%ENDTIME%") DO ( SET /A "end=(((%%a*60)+1%%b %% 100)*60+1%%c %% 100)*100+1%%d %% 100" ) REM Calculate the elapsed time by subtracting values SET /A elapsed=end-start REM Format the results for output SET /A hh=elapsed/(60*60*100), rest=elapsed%%(60*60*100), mm=rest/(60*100), rest%%=60*100, ss=rest/100, cc=rest%%100 IF %hh% lss 10 SET hh=0%hh% IF %mm% lss 10 SET mm=0%mm% IF %ss% lss 10 SET ss=0%ss% IF %cc% lss 10 SET cc=0%cc% SET DURATION=%hh%:%mm%:%ss%,%cc% ECHO Migration Complete ECHO Start : %STARTTIME% ECHO Finish : %ENDTIME% ECHO --------------- ECHO Duration : %DURATION% GOTO End :ErrorFileMissing ECHO WARNING: one of the required file is missing, please check if they exists at the location provided GOTO End :ErrorHosting ECHO WARNING: You must provide an IP if you use_another_machine is set to 1 GOTO End :ErrorConfig ECHO WARNING: You should edit this file, you forgot to put database name, username or password. GOTO End :End Save the file stored_functions.sql Create a new file "stored_functions.sql" Paste the following code DROP FUNCTION IF EXISTS `SENDABLE`; DELIMITER $$ CREATE FUNCTION SENDABLE(email varchar(100),mobile varchar(30)) RETURNS VARCHAR(10) DETERMINISTIC BEGIN DECLARE isSendable int(1); SET @isSendable = 0; IF ((email IS NULL || email = '' || email = '-' || email NOT LIKE '%@%.%')&&(mobile IS NULL || mobile = '' || mobile = '-')) THEN SET @isSendable = 0; ELSE IF ((email IS NOT NULL && (email != '' && email != '-' && email LIKE '%@%.%'))) THEN SET @isSendable = @isSendable + 1; END IF; IF ((mobile IS NOT NULL && (mobile != '' && mobile != '-'))) THEN SET @isSendable = @isSendable + 2; END IF; END IF; SET isSendable = @isSendable; #if customer have only email = 1, only mobile = 2, both = 3 RETURN (isSendable); END; Save the file